﻿Imports System.Xml.Linq
Imports CrystalReportViewer
Imports CrystalDecisions.CrystalReports.Engine

Public Class IICReportsDataSourceHandler
    Implements ICriterionUIHandler
    Implements IReportInstanceLoader

    Private Shared _instance As IICReportsDataSourceHandler

    Private Sub IICReportsDataSourceHandler()
        'make Singleton
    End Sub

    Public Shared Function instance() As IICReportsDataSourceHandler

        If (_instance Is Nothing) Then
            _instance = New IICReportsDataSourceHandler

        End If
        Return _instance

    End Function

    Public Overridable Sub processCriterion(ByVal ctrl As System.Windows.Forms.Control, _
                             ByVal crit As CriterionMetadata) _
                Implements ICriterionUIHandler.renderControlHandler

        If crit.Name = "showGrade" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            Dim obj As XDocument = <?xml version="1.0"?>
                                   <booleans>
                                       <entry Id="0" Description="ΟΧΙ"/>
                                       <entry Id="1" Description="ΝΑΙ"/>
                                   </booleans>

            Dim ds As New DataSet
            ds.ReadXml(obj.CreateReader)
            c.DataSource = ds.Tables(0)
            c.DisplayMember = "Description"
            c.ValueMember = "Id"


        ElseIf crit.Name = "LessonId" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            AddHandler c.SelectedIndexChanged, AddressOf Lesson_SelectedIndexChanged

        ElseIf crit.Name = "DiplomaSuperTypeId" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            Dim sql = <string>
select null as [DiplomaSuperTypeId], '-- All --' as [DiplomaSuperTypeName], '' as [sortExpression]
union 
select [DiplomaSuperTypeId],[DiplomaSuperTypeCode] + ' ' + [DiplomaSuperTypeName] as DiplomaSuperTypeName , 
    DiplomaSuperTypeCode as [sortExpression] from [DiplomaSuperType] order by sortExpression 
</string>
            c.DataSource = DBUtils.Current.getDataTable(sql.Value)
            c.DisplayMember = "DiplomaSuperTypeName"
            c.ValueMember = "DiplomaSuperTypeId"

            AddHandler c.SelectedIndexChanged, AddressOf DiplomaSuperType_SelectedIndexChanged


        ElseIf crit.Name = "IsMemberActive" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            Dim obj As XDocument = <?xml version="1.0"?>
                                   <booleans>
                                       <entry Id="" Description="-- All --"/>
                                       <entry Id="0" Description="NO"/>
                                       <entry Id="1" Description="YES"/>
                                   </booleans>

            Dim ds As New DataSet
            ds.ReadXml(obj.CreateReader)
            ds.AcceptChanges()
            c.DataSource = ds.Tables(0)
            c.DisplayMember = "Description"
            c.ValueMember = "Id"

        ElseIf crit.Name = "Company" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            Call addCompanyDataSource(c, crit)

        ElseIf crit.Name = "MemberCompany" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            Call addMemberCompanyDataSource(c, crit)

        ElseIf crit.Name = "PeriodId" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            AddHandler c.SelectedIndexChanged, AddressOf Period_SelectedIndexChanged

        ElseIf crit.Name = "LecturePeriodId" Then
            Dim c As ComboBox = CType(ctrl, ComboBox)
            AddHandler c.SelectedIndexChanged, AddressOf LecturePeriod_SelectedIndexChanged

        End If

    End Sub

    Private Sub resetLecturePeriodComboBoxFromDiplomaSuperType(ByVal filterSQL As String, ByVal f As Form)
        Dim cs As Control() = f.Controls.Find("LecturePeriodId", True)
        If (cs.Length = 0) Then
            Return
        End If

        Dim periodcbox As CGComboBox = CType(cs(0), CGComboBox)
        Dim sql = <string>
		select null as [PeriodId], '-- All --' as [PeriodName] , '3000-01-01' as PeriodDate
		union
		select a.[PeriodId], a.[PeriodCode] + ' ' + a.[PeriodName] , a.PeriodDate from [Period] a, Lecture b
		where a.PeriodId = b.PeriodId
        and <%= filterSQL %> 
		order by a.PeriodDate desc
		</string>

        periodcbox.DataSource = DBUtils.Current.getDataTable(sql.Value)
        periodcbox.DisplayMember = "PeriodName"
        periodcbox.ValueMember = "PeriodId"
        Call autoSelectFirstItem(periodcbox)

    End Sub

    Private Sub resetPeriodComboBoxFromDiplomaSuperType(ByVal filterSQL As String, ByVal f As Form)

        Dim cs As Control() = f.Controls.Find("PeriodId", True)
        If (cs.Length = 0) Then
            Return

        End If

        Dim periodcbox As CGComboBox = CType(cs(0), CGComboBox)
        Dim sql = <string>
		select null as [PeriodId], '-- All --' as [PeriodName] , '3000-01-01' as PeriodDate
		union
		select [PeriodId], [PeriodCode] + ' ' + [PeriodName] , PeriodDate from [Period]
		where <%= filterSQL %>
		order by PeriodDate desc
		</string>

        periodcbox.DataSource = DBUtils.Current.getDataTable(sql.Value)
        periodcbox.DisplayMember = "PeriodName"
        periodcbox.ValueMember = "PeriodId"
        Call autoSelectFirstItem(periodcbox)

    End Sub

    Private Sub resetCerificateComboBoxFromDiplomaSuperType(ByVal filterSQL As String, ByVal f As Form)

        Dim cs As Control() = f.Controls.Find("DiplomaTypeId", True)
        If (cs.Length = 0) Then
            Return

        End If

        Dim certBox As CGComboBox = CType(cs(0), CGComboBox)
        Dim sql = <string>
		select null as DiplomaTypeId, '-- All --' as DiplomaType 
union
select s.DiplomaTypeId, s.DiplomaType from DiplomaType s
where <%= filterSQL %>
order by DiplomaType asc
		</string>

        certBox.DataSource = DBUtils.Current.getDataTable(sql.Value)
        certBox.DisplayMember = "DiplomaType"
        certBox.ValueMember = "DiplomaTypeId"
        Call autoSelectFirstItem(certBox)

    End Sub

    Private Sub DiplomaSuperType_SelectedIndexChanged(ByVal sender As Object, _
                                               ByVal e As EventArgs)

        Dim filterSQL As String = String.Empty
        Dim cbox As ComboBox = CType(sender, ComboBox)
        If cbox.SelectedItem Is Nothing Then
            filterSQL = "1=0"
        Else
            Dim selItem As DataRow = CType(cbox.SelectedItem, DataRowView).Row
            If selItem Is Nothing OrElse selItem.Item(0) Is Nothing OrElse IsDBNull(selItem.Item(0)) Then
                filterSQL = "1=0"
            Else
                Dim diplomaSuperTypeid As Integer = CType(selItem.Item(0), Integer)
                filterSQL = "DiplomaSuperTypeId=" & diplomaSuperTypeid
            End If

        End If

        Dim f As Form = cbox.FindForm

        Call resetPeriodComboBoxFromDiplomaSuperType(filterSQL, f)
        Call resetLecturePeriodComboBoxFromDiplomaSuperType(filterSQL, f)
        Call resetCerificateComboBoxFromDiplomaSuperType(filterSQL, f)

    End Sub

    Private Sub LecturePeriod_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim cbox As ComboBox = CType(sender, ComboBox)
        Dim f As Form = cbox.FindForm
        Dim cs As Control() = f.Controls.Find("LessonId", True)

        If cs Is Nothing Then Return
        If cs.Length = 0 Then Return

        Dim lessoncbo As ComboBox = CType(cs(0), ComboBox)
        Dim ldr As DataRowView = CType(cbox.SelectedItem, DataRowView)

        If ldr IsNot Nothing AndAlso IsDBNull(ldr.Row.Item(0)) = False Then

            Dim periodid As String = CStr(ldr.Row.Item(0))

            Dim sql = <ssql>
select null as LessonId, '-- All --' as LessonName, '' as [sortExpression]
union 
SELECT     s.LessonId, s.LessonCode + ' ' + s.LessonName AS LessonName, s.LessonCode AS sortExpression
FROM         Lesson AS s INNER JOIN
                      Lecture AS c ON s.LessonId = c.LessonId INNER JOIN
                      Period AS p ON c.PeriodId = p.PeriodId
WHERE     (p.PeriodId = <%= periodid %>)
ORDER BY sortExpression
                      </ssql>

            lessoncbo.DataSource = DBUtils.Current.getDataTable(sql.Value)
            lessoncbo.DisplayMember = "LessonName"
            lessoncbo.ValueMember = "LessonId"
        Else
            lessoncbo.DataSource = Nothing
        End If

        Call autoSelectFirstItem(lessoncbo)
    End Sub

    Private Sub Period_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

        Dim cbox As ComboBox = CType(sender, ComboBox)
        Dim f As Form = cbox.FindForm
        Dim cs As Control() = f.Controls.Find("LessonId", True)

        If cs Is Nothing Then Return
        If cs.Length = 0 Then Return

        Dim lessoncbo As ComboBox = CType(cs(0), ComboBox)
        Dim ldr As DataRowView = CType(cbox.SelectedItem, DataRowView)

        If ldr IsNot Nothing AndAlso IsDBNull(ldr.Row.Item(0)) = False Then

            Dim periodid As String = CStr(ldr.Row.Item(0))

            Dim sql = <ssql>
select null as LessonId, '-- All --' as LessonName, '' as [sortExpression]
union 
SELECT     s.LessonId, s.LessonCode + ' ' + s.LessonName AS LessonName, s.LessonCode AS sortExpression
FROM         Lesson AS s INNER JOIN
                      Class AS c ON s.LessonId = c.LessonId INNER JOIN
                      Period AS p ON c.PeriodId = p.PeriodId
WHERE     (p.PeriodId = <%= periodid %>)
ORDER BY sortExpression
                      </ssql>

            lessoncbo.DataSource = DBUtils.Current.getDataTable(sql.Value)
            lessoncbo.DisplayMember = "LessonName"
            lessoncbo.ValueMember = "LessonId"
        Else
            lessoncbo.DataSource = Nothing
        End If

        Call autoSelectFirstItem(lessoncbo)

    End Sub

    Private Sub Lesson_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        'Dim cbox As ComboBox = CType(sender, ComboBox)
    End Sub

    Private Sub autoSelectFirstItem(ByVal c As ComboBox)

        If c.DataSource IsNot Nothing Then
            'if count =2, means only one item came back, since the first item is "please select"
            If c.Items.Count = 2 Then
                c.SelectedIndex = 1
            Else
                c.SelectedIndex = 0
            End If
        Else
            c.SelectedIndex = -1
        End If

    End Sub

    Private Sub addCompanyDataSource(ByVal c As ComboBox, ByVal crit As CriterionMetadata)
        Dim sql = <ssql>
select '' as id, '-- All --' as Descr
union 
select s.company as id , s.company as Descr from student s 
where isnull(s.Company,'')&lt;&gt;''
group by s.company
order by 1
</ssql>
        c.DataSource = DBUtils.Current.getDataTable(sql.Value)
        c.DisplayMember = "Descr"
        c.ValueMember = "id"

    End Sub

    Private Sub addMemberCompanyDataSource(ByVal c As ComboBox, ByVal crit As CriterionMetadata)
        Dim sql = <ssql>
select '' as id, '-- All --' as Descr
union 
select s.company as id , s.company as Descr from student s  where s.isMember=1 and s.IsMemberActive=1
and isnull(s.Company,'')&lt;&gt;''
group by s.company
order by 1
</ssql>
        c.DataSource = DBUtils.Current.getDataTable(sql.Value)
        c.DisplayMember = "Descr"
        c.ValueMember = "id"

    End Sub

    Public Sub controlValueHandler(ByRef value As Object, _
                                   ByVal c As System.Windows.Forms.Control, _
                                   ByVal crit As CrystalReportViewer.CriterionMetadata) _
                                                                Implements CrystalReportViewer.ICriterionUIHandler.controlValueHandler

    End Sub

    Public Sub controlDisplayValueHandler(ByRef value As String, _
                                          ByVal c As System.Windows.Forms.Control, _
                                          ByVal crit As CrystalReportViewer.CriterionMetadata) _
                                                Implements CrystalReportViewer.ICriterionUIHandler.controlDisplayValueHandler

    End Sub

    Public Function getReportInstance(ByVal rm As CrystalReportViewer.ReportMatadata) As ReportClass _
                    Implements CrystalReportViewer.IReportInstanceLoader.getReportInstance


        Dim lrptClass As String = rm.FullyQualifiedResourcePath

        If lrptClass = "IIC.rptDeltio" Then
            Dim diplomaSuperTypeId As Object = rm.getCriterion("DiplomaSuperTypeId").selectedvalue
            Dim periodId As Object = rm.getCriterion("PeriodId").selectedvalue

            If diplomaSuperTypeId IsNot Nothing AndAlso CStr(diplomaSuperTypeId) = "1" Then
                Return New rptDeltioPBAK
            Else
                If periodId IsNot Nothing Then
                    If PeriodDataUtils.findByKey(CInt(periodId)).DiplomaSuperTypeId = 1 Then
                        Return New rptDeltioPBAK

                    End If
                End If
            End If

        ElseIf lrptClass = "IIC.rptResultsByPeriodLesson" Then
            Dim diplomaSuperTypeId As Object = rm.getCriterion("DiplomaSuperTypeId").selectedvalue
            Dim periodId As Object = rm.getCriterion("PeriodId").selectedvalue

            If diplomaSuperTypeId IsNot Nothing AndAlso CStr(diplomaSuperTypeId) = "1" Then
                Return New rptResultsByPeriodLessonPVAK
            Else
                If periodId IsNot Nothing Then
                    If PeriodDataUtils.findByKey(CInt(periodId)).DiplomaSuperTypeId = 1 Then
                        Return New rptResultsByPeriodLessonPVAK

                    End If
                End If
            End If

        End If

        Return CType(AppDomain.CurrentDomain.CreateInstanceAndUnwrap(rm.ReportAssemblyName, _
                        lrptClass), ReportClass)


    End Function

   

End Class

